#!/usr/bin/env python
# -*- coding: UTF-8 -*-
'''
@Project ：大路网网格化仿真
@File ：chuangao_deal.py
@IDE ：PyCharm
@Author ：gj
@Date ：2025/2/17 18:00
'''
import openpyxl
import os
# from redis_utils import send_redis_hset, get_redis_hset
import pandas as pd


def get_gantary_id(event_file_path, up_name, down_name):
    gantary_id_dict = {}
    # 读取 Excel 文件
    df = pd.read_excel(event_file_path)
    # 遍历 DataFrame 的每一行
    for index, row_i in df.iterrows():
        up = str(row_i[up_name])
        down = str(row_i[down_name])
        # if down == "G006551001001220010":
        #     continue
        if up not in gantary_id_dict.keys():
            gantary_id_dict[up] = {}
            gantary_id_dict[up][down] = {}
            gantary_id_dict[up][down]["list"] = []
        elif down not in gantary_id_dict[up].keys():
            gantary_id_dict[up][down] = {}
            gantary_id_dict[up][down]["list"] = []

        event_day = {
            "year": str(row_i["年"]),
            "month": '{:02d}'.format(row_i["月"]),
            "day": '{:02d}'.format(row_i["日"]),
        }
        if event_day not in gantary_id_dict[up][down]["list"]:
            gantary_id_dict[up][down]["list"].append(event_day)

    return gantary_id_dict


def save_gantary_data(folder_path, id_dict, save_path, up_name, down_name):
    up_folder_path = os.path.join(folder_path, up_name)
    down_folder_path = os.path.join(folder_path, down_name)
    for k, v in id_dict.items():
        try:
            up_path = os.path.join(up_folder_path, k)
            for d, l in v.items():
                down_path = os.path.join(down_folder_path, d)
                for day in l["list"]:
                    up_file_path = os.path.join(up_path, f"{k}_{day['year']}-{day['month']}-{day['day']}.xlsx")
                    down_file_path = os.path.join(down_path, f"{d}_{day['year']}-{day['month']}-{day['day']}.xlsx")
                    new_file_path = os.path.join(save_path, f"{k},{d}-{day['year']}{day['month']}{day['day']}.csv")
                    # if os.path.exists(new_file_path):
                    #     print(f"{new_file_path} exists")
                    #     continue
                    if not os.path.exists(up_file_path):
                        print(f"{up_file_path} not exists")
                        up_file_path = os.path.join(up_path, f"{k}{day['year']}-{day['month']}-{day['day']}.xlsx")
                        if not os.path.exists(up_file_path):
                            print(f"{up_file_path} not exists")
                            continue

                    if not os.path.exists(down_file_path):
                        print(f"{down_file_path} not exists")
                        continue
                    df_up = pd.read_excel(up_file_path, engine='openpyxl')
                    df_down = pd.read_excel(down_file_path, engine='openpyxl')
                    gantryid_up = df_up["门架编号"]
                    vlp_up = df_up["车牌号"]
                    # df_up["车型"] = 1
                    feevehicletype_up = df_up["车型"]
                    transtime_up = df_up["过车时间"]
                    gantryid_down = df_down["门架编号"]
                    vlp_down = df_down["车牌号"]
                    # df_down["车型"] = 1
                    feevhicletype_down = df_down["车型"]
                    transtime_down = df_down["过车时间"]
                    # 如果列名相同，将两列数据合并
                    combined_data_gantryid = pd.concat([gantryid_up, gantryid_down], ignore_index=True)
                    combined_data_vlp = pd.concat([vlp_up, vlp_down], ignore_index=True)
                    combined_data_feevehicletype = pd.concat([feevehicletype_up, feevhicletype_down], ignore_index=True)
                    combined_data_transtime = pd.concat([transtime_up, transtime_down], ignore_index=True)
                    # 创建新的 DataFrame
                    new_df = pd.DataFrame({
                        "gantryid": combined_data_gantryid,
                        "vlp": combined_data_vlp,
                        "feevehicletype": combined_data_feevehicletype,
                        "transtime": combined_data_transtime
                    })
                    # 将新的 DataFrame 保存为 CSV 文件
                    new_df.to_csv(new_file_path, index=False)
                    print(f"数据已成功保存到 {new_file_path}")
        except FileNotFoundError:
            print("错误: 文件未找到，请检查文件路径。")
        except KeyError:
            print("错误: 指定的列名不存在，请检查列名。")
        except Exception as e:
            print(f"发生未知错误: {e}")


if __name__ == '__main__':
    # file_path = r'D:\GJ\项目\事故检测\xlsx\成德南公司金堂段11.xlsx'
    # data_folder = r"D:\GJ\项目\事故检测\川高事故门架数据\事故门架数据\成德南公司金堂段\交易流水"
    # save_path = r"D:\GJ\项目\事故检测\成德南公司金堂段"
    # if not os.path.exists(save_path):
    #     os.makedirs(save_path)
    #
    # Gid_dict = get_gantary_id(file_path)
    # print(Gid_dict)
    # # c = 0
    # # for k, v in Gid_dict.items():
    # #     for d, l in v.items():
    # #         c += len(l["list"])
    # # print(c)
    # save_gantary_data(data_folder, Gid_dict, save_path)

    file_path = r'D:\GJ\项目\事故检测\xlsx\达陕公司达陕段11.xlsx'
    data_folder = r"D:\GJ\项目\事故检测\川高事故门架数据\事故门架数据\达陕公司达陕段\牌识流水"
    save_path = r"D:\GJ\项目\事故检测\达陕公司达陕段"
    if not os.path.exists(save_path):
        os.makedirs(save_path)

    Gid_dict = get_gantary_id(file_path)
    print(Gid_dict)
    c = 0
    for k, v in Gid_dict.items():
        for d, l in v.items():
            c += len(l["list"])
    print(c)
    save_gantary_data(data_folder, Gid_dict, save_path, "事故最近门架编号流水2", "事故最近门架编号流水1")

    # file_path = r'D:\GJ\项目\事故检测\xlsx\攀西公司泸黄段11.xlsx'
    # data_folder = r"D:\GJ\项目\事故检测\川高事故门架数据\事故门架数据\攀西公司泸黄段\交易流水"
    # save_path = r"D:\GJ\项目\事故检测\攀西公司泸黄段"
    # if not os.path.exists(save_path):
    #     os.makedirs(save_path)
    #
    # Gid_dict = get_gantary_id(file_path)
    # print(Gid_dict)
    # save_gantary_data(data_folder, Gid_dict, save_path)

